Acquiring physical locks on a transaction log of a database is a resource-intensive operation and has performance implications in terms of database scalability. Database applications rely heavily on acquiring physical locks in database transaction logs in order to guarantee cache coherency.
As the number of nodes increase with large database management system (DBMS) applications, physical lock acquisition becomes more expensive.
As the number of nodes in a database environment increases, physical lock acquisition and management become more expensive. When a serialized transaction log stream is shared amongst many nodes, a performance bottleneck can arise. The resources needed to acquire and manage a large number of transaction log locks across multiple nodes can limit the ability to scale or grow a database. As distributed, multi-node databases are growing in size and complexity, what is needed are methods and systems that efficiently acquire and manage transaction log locks.
Transaction logs are critical resources when it comes to growing or scaling databases. There have been several features and enhancements done in the past to improve the performance of transaction logging. Many database management system (DBMS) platforms with a cluster comprised of multiple nodes employ shared disks to improve data reliability and to enable features such as database mirroring and data replication. When shared disks are used, database transaction logs are even more critical as they are the single point of contention between the nodes in the cluster.
Currently in the art, physical locks are taken on all data and transaction log pages of a database. There is overhead associated with taking physical locks on data pages. In order to maintain buffer cache coherency and to synchronize access to data and transaction log pages across multiple nodes, a module must take physical locks. The steps involved every time a physical lock is taken on a page often include the following: a requester node takes the cache spinlock, setting/unsetting the cluster-specific status bits pertaining to a group of buffers which are controlled together in the cache. A MASS unit may be attached to control a group of buffers. The specific physical lock request goes to Cluster Lock Manager (CLM) module via a call to the lock_multiple_physical( ) routine, the CLM sends a blocking asynchronous trap (BAST) request to the BCM thread of the owner node. A BAST request is an asynchronous event issued by a lock manager that manages physical lock requests. After the BAST request is issued, the request is queued, the owner downgrades its lock as needed, and the owner transfers the transaction log page to the requester. Each of these steps requires some execution time, and thus impact system performance. There is also space overhead as the physical locks are retention locks. Each successfully taken physical lock consumes a memory location permanently.
Furthermore, each time a physical lock is taken; it adds a LOCKREC element into the grant/convert queue of the CLM, which in turn increases the search time for a given LOCKREC. Accordingly, what is desired is a means of efficiently acquiring and managing transaction log locks. What is further needed is a protocol that enables more efficient physical lock acquisition for transaction log pages. What is further desired are systems and methods that help eliminate expensive operations during database transaction logging and enables better database scaling.
Accordingly, what is needed are methods, systems, and computer program products that optimize database transaction log lock acquisition. What is further needed are methods, systems, and computer program products that optimize transaction log lock optimization by avoiding physical lock acquisition for new transaction log page allocations, including the last transaction log page.